How RIGHT JOIN works in SQL? Best RIGHT JOIN examples

How RIGHT JOIN works in SQL? Best RIGHT JOIN examples

When working with SQL queries, joining tables is one of the most fundamental techniques we use to retrieve meaningful data from a relational database. One of the available join types is the RIGHT JOIN, which allows us to combine records from two tables while ensuring that all rows from the right table are included in the result set. In this article, I’ll explain how RIGHT JOIN works, when to use it, and provide some of the best RIGHT JOIN examples.

What Is RIGHT JOIN in SQL?

The RIGHT JOIN, sometimes referred to as RIGHT OUTER JOIN, returns all records from the right table and only the matching records from the left table. If there is no match, NULL values are returned for columns from the left table. This type of join is useful when we need to keep all the records from the right table, regardless of whether there is a corresponding record in the left table.

RIGHT JOIN Syntax

The basic syntax of a RIGHT JOIN query is as follows:

SELECT column_names
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

Here’s what happens in this query:

  • table1 is the left table.
  • table2 is the right table.
  • The ON clause specifies the condition to match records.
  • All records from table2 (right table) will appear in the output.
  • Rows from table1 (left table) will only appear if a corresponding match exists; otherwise, NULL will be returned for those columns.

RIGHT JOIN vs LEFT JOIN: What’s the Difference?

To better understand RIGHT JOIN, it helps to compare it with LEFT JOIN:

Join Type Description
LEFT JOIN Returns all records from the left table and only the matching records from the right table. If no match is found, NULL is returned for the right table’s columns.
RIGHT JOIN Returns all records from the right table and only the matching records from the left table. If no match is found, NULL is returned for the left table’s columns.

Best RIGHT JOIN Examples

Now, let’s look at some practical RIGHT JOIN examples.

Example 1: RIGHT JOIN with Customer and Orders Table

Consider two tables, Customers and Orders. Our goal is to retrieve all orders along with their corresponding customer information.

Customers Table:

CustomerID Name City
1 John Doe New York
2 Jane Smith Los Angeles

Orders Table:

OrderID CustomerID Amount
101 1 500
102 NULL 300

Now, let’s apply a RIGHT JOIN:

SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.Amount
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Result Set:

CustomerID Name OrderID Amount
1 John Doe 101 500
NULL NULL 102 300

Since the Orders table is on the right side of the join, all orders are included in the result even when there is no matching customer.

Example 2: RIGHT JOIN with Employees and Departments

Let’s say we have employees assigned to different departments. We need to list all departments and the employees assigned to them. If a department has no employees, we still want it in the result.

SELECT Employees.Name, Employees.Position, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

This ensures that all departments appear in the result, even if they do not currently have employees assigned.

When Should You Use RIGHT JOIN?

RIGHT JOIN is particularly useful in the following scenarios:

  • When you need to ensure all records from the right table are included, even if there are no matches in the left table.
  • When working with reports or analysis that require displaying all entries from a reference table, such as orders, even if they don’t have related entries in another table.
  • When handling data migrations where you want to check if certain records exist across tables.

Conclusion

Understanding how RIGHT JOIN works in SQL is essential when dealing with complex queries where you need to retain all records from one table while selectively including matching records from another. Whether you are working with customer orders, employee records, or financial transactions, applying RIGHT JOIN correctly can help you retrieve the information you need while maintaining data integrity.

 

Other interesting article:

How LEFT JOIN works in SQL? Best LEFT JOIN examples